﻿SET QUOTED_IDENTIFIER ON
GO
SET ARITHABORT ON
GO
SET NUMERIC_ROUNDABORT OFF
GO
SET CONCAT_NULL_YIELDS_NULL ON
GO
SET ANSI_NULLS ON
GO
SET ANSI_PADDING ON
GO
SET ANSI_WARNINGS ON
GO

-- =============================================
-- Author:		Adrian Kaczmarek
-- Create date: 2013-08-16
-- =============================================
CREATE PROCEDURE [dbo].[bhp_NemoGroupDelete] (
	@GroupId			INT
)
WITH ENCRYPTION
AS
BEGIN

	DECLARE 
		@TableName			NVARCHAR(400),
		@Sql						NVARCHAR(4000)

	UPDATE
		n_Tree
	SET
		del = 1,
		mtime = GETDATE()
	WHERE
		id = @GroupId

	SELECT
		@TableName = name
	FROM
		n_Tree
	WHERE
		id = @GroupId

	WHILE EXISTS(SELECT 1 FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS WHERE CONSTRAINT_CATALOG = DB_NAME() and TABLE_NAME = @TableName)
	BEGIN
		SELECT 
			@Sql = 'ALTER TABLE ' + @TableName + ' DROP CONSTRAINT ' + CONSTRAINT_NAME 
		FROM    
			INFORMATION_SCHEMA.TABLE_CONSTRAINTS 
		WHERE    
			CONSTRAINT_CATALOG = DB_NAME() 
			AND TABLE_NAME = @TableName
    
		EXEC sp_executesql @Sql

	END

	SET @Sql = 'DROP TABLE ' + @TableName

	EXEC sp_executesql @Sql

END
